<?php

namespace App\Libs\PhpOffice;

//文件的导出和导入
class Excel
{

    public $pic = '';
    public $str = '';

    public function __construct()
    {
        try {
            ini_set('memory_limit', '4096M');
            ini_set('max_execution_time', '600');
        } catch (\Exception $e) {
        }
        //PhpSpreadsheet 引入
        require 'vendor/autoload.php';
    }
    //导出文件

    /**
     *
     *
     * @param [type] $data   插入表格的数据
     * @param [type] $title  表格的标题
     * @param string $fielname 文件名称
     * @return void
     */
    public function exportFile($data, $title, $fielname = '')
    {
        if (empty($fielname)) {
            $fielname = 'attach/excel/' . date('YmdHis') . '.xlsx';
        }
        $file = substr($fielname, 0, strrpos($fielname, "/"));
        confirmDir($file);
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $abc = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN'];
        // $sheet->setCellValue('A1', '用户id');
        if (!empty($title)) {
            foreach ($title as $key => $v) {
                $sheet->setCellValue($abc[$key] . '1', $v);
            }
        }
        $i = 2;

        if (!empty($data)) {
            foreach ($data as $k => $v) {
                $j = 0;
                if (!empty($v)) {
                    foreach ($v as $key => $vv) {
                        // if (empty($abc[$key])) {
                        //   $sheet->setCellValue($abc[$key] . $i, $vv);
                        // } else {
                        // }
                        if ($vv && strpos($vv, '=') === 0) {
                            // 在 = 前面加个单引号
                            $vv = "'" . $vv;
                        }
                        $sheet->setCellValue($abc[$j] . $i, $vv . ' ');
                        $j++;
                    }
                }
                $i++;
            }
        }
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $dir = base_path() . "/public" . '/' . $fielname;
        $writer->save($dir); //写入excel文件并保存
        return $fielname;
    }
    //导入文件

    /**
     *
     *
     * @param [type] $fielName   读取文件的名称
     * @param array $field 文件的字段
     * @return void
     */
    public function importFile($fielName, $field = [], $function = '')
    {
        $flag = true;
        $msg = '导入成功';
        $failData = [];
        if (empty($fielName)) {
            $flag = false;
            $msg = '文件名称不能为空';
        }
        if (empty($fielName) || !file_exists($fielName)) {
            $flag = false;
            $msg = '文件不存在' . $fielName;
        }
        if ($flag) {
            $fileExtendName = substr(strrchr($fielName, '.'), 1);
            if ($fileExtendName == 'xlsx') {
                // var_dump(222222222);
                $resad = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
            } else {
                // var_dump(333333333);
                $resad = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');
            }
            if (!$resad->canRead($fielName)) {
                $resad = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');
            }
            //读取文件

            $data = $resad->load($fielName);
            //读取数据
            $spreadsheet = $data->getActiveSheet();
            // 取得总行数
            $highestRow = $spreadsheet->getHighestRow();
            //获得总列数
            $highestColumn = $spreadsheet->getHighestColumn();
            $data = [];

            $i = 0;
            $nums = 0;
            $abc = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
            //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
            for ($j = 2; $j <= $highestRow; $j++) {
                $nums = 0;
                $readData = [];
                foreach ($abc as $v) {
                    $name = '';
                    if (!empty($field) && is_array($field)) {
                        $name = !empty($field[$nums]) ? $field[$nums] : '';
                    }
                    if (empty($name)) {
                        break;
                    }
                    $readData = $spreadsheet->getCell($v . $j)->getValue();
                    $data[$i][$name] = $readData;
                    if ($v == $highestColumn) {
                        break;
                    }
                    $nums++;
                }
                if (!empty($function) && is_callable($function)) {
                    if ($function($data[$i]) === true) {
                        $failData[] = $data[$i];
                        unset($data[$i]);
                    } else {
                        $data[$i] = $function($data[$i]);
                    }
                }
                $i++;
            }
            if (empty($data)) {
                $flag = false;
                $msg = '上传的文件为空';
            }
            if (empty($data) && !empty($failData)) {
                $msg = '执行成功的文件为空';
            }
        }
        if ($flag) {
            return [
                'code' => 200,
                'msg' => $msg,
                'data' => $data,
                'fail_data' => $failData
            ];
        }
        return [
            'code' => 102,
            'msg' => $msg,
            'data' => [],
            'fail_data' => $failData,
        ];
    }
}
